﻿using System;
using AdminHome.Models;
using System.Collections.Generic;
namespace AdminHome.DatabaseServices
{//Services for DB layer not integrated into CRUD
    public class AdminHomeDatabaseMethods : IAdminHomeDatabaseMethods
    {
        public static string createAsset(string departmentID, string subCategoryID, string productID)
        {
            var temp = MVCSiteJDG.Dao.DataAccess.Creator.createAsset(departmentID, subCategoryID, productID);
            if (temp == true)
            {
                return "asset success";
            }
            return "asset fail";
        }

        public static string createAccount(string accountName, string accountPassword, string accountRegDate, string accountLastLogin, string accountEmail, string accountContactNumber, string accountBalance, string costCentreID)
        {
            var temp = MVCSiteJDG.Dao.DataAccess.Creator.createAccount(accountName, accountPassword, accountRegDate, accountLastLogin, accountEmail, accountContactNumber, accountBalance, costCentreID);
            if (temp == true)
            {
                return "Account success";
            }
            return "Account fail";
        }
        public static string createDepartment(string building, string floor, string room, string costCentreID)
        {
            var temp = MVCSiteJDG.Dao.DataAccess.Creator.createDepartment(building, floor, room, costCentreID);
            if (temp == true)
            {
                return "department success";
            }
            return "department fail";
            
        }
        public static string createProduct(string productName, string productDescription, string productPrice, string productImage, string productInDate, string catID)
        {
             var temp = MVCSiteJDG.Dao.DataAccess.Creator.createProduct(productName, productDescription, productPrice, productImage, productInDate, catID);
             if (temp == true)
             {
                 return "product success";
             }
             return"product fail";
        }

        public static string createLineItem(string productID, string productQuantity, string orderID)
        {
            var temp = MVCSiteJDG.Dao.DataAccess.Creator.createOrderLineItem(productID, productQuantity, orderID);
            if (temp == true)
            {
                return "lineITem success";
            }
            return "lineItem fail";
        }
        public static string createRequest(string accountID, string technicianID, string requestMessage)
        {
           var temp = MVCSiteJDG.Dao.DataAccess.Creator.createRequest(accountID, technicianID, requestMessage);
           if (temp == true)
           {
               return "request success";
           }
           return "request fail";
        }

        public static string createSupplier(string supplierName)
        {
            var temp = MVCSiteJDG.Dao.DataAccess.Creator.createSupplier(supplierName);
            if (temp == true)
            {
                return "supplier success";
            }
            return "supplier fail";
        }
        public static string createShoppingCart(string shoppingDate, string accountID)
        {
            var temp = MVCSiteJDG.Dao.DataAccess.Creator.createShoppingCart(shoppingDate,accountID);
            if (temp == true)
            {
                return "shoppingcart success";
            }
            return "shoppingcart fail";
        }
        public static string createMainCategory(string description)
        {
            var temp = MVCSiteJDG.Dao.DataAccess.Creator.createMainCategory(description);
            if (temp == true)
            {
                return "main category success";
            }
            return "main category fail";
        }
        public static string createOrder(string orderStatus, string orderTotalCost, string departmentID)
        {
           var temp = MVCSiteJDG.Dao.DataAccess.Creator.createOrders(orderStatus, orderTotalCost, departmentID);
           if (temp == true)
           {
               return "order success";
           }
           else
           {
               return "order fail";
           }
        }
        public static string createLog(string requestId, string message)
        {
            var temp = MVCSiteJDG.Dao.DataAccess.Creator.createLog(requestId,message);
            if (temp == true)
            {
                return "log success";
            }
            else
            {
                return "log fail";
            }
        }
        public static List<List<string>> getAllLocations()
        {
           var data = MVCSiteJDG.Dao.DataAccess.Reader.readAllDepartment();
           return data;
        }
        public static String searchLocations(String LocationDescription)
        {

            return null;
        }
        public static String getAccountId(String UserName)
        {
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand getAccountIdCommand = new System.Data.OleDb.OleDbCommand("select accountID from Account where accountName = '" + UserName + "'", connection);
            connection.Open();
            System.Data.OleDb.OleDbDataReader reader = getAccountIdCommand.ExecuteReader();
            String[] SqlData = new String[1];
            while (reader.Read())
            {
                SqlData[0] = reader.GetValue(0).ToString();
            }
            var returnThis = SqlData[0].ToString();  

            reader.Dispose();
            return returnThis;
        }

        public static String getAccountDetails(String UserName)
        {
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");
            System.Data.OleDb.OleDbCommand getAccountDetailsCommand = new System.Data.OleDb.OleDbCommand("select accountName, accountRegDate, accountLastLogin, accountEmail from Account where accountID = '" + UserName + "'", connection);
            connection.Open();
            System.Data.OleDb.OleDbDataReader reader = getAccountDetailsCommand.ExecuteReader();
            String[] SqlData = new String[5];
            while (reader.Read())
            {

                SqlData[0] = reader.GetString(0);
                SqlData[1] = reader.GetString(1);
                SqlData[2] = reader.GetString(2);
                SqlData[3] = reader.GetString(3);
            }
            var returnThis = " name = " + SqlData[0] + "registration date" + SqlData[1] + "last login" + SqlData[2] + "email" + SqlData[3];  

            reader.Dispose();
            return returnThis;
        }

        public static String getProductList(int i, int i2)
        {
            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");

            System.Data.OleDb.OleDbCommand getAccountDetailsCommand = new System.Data.OleDb.OleDbCommand("select * from product where rowID >" + i + " and rowID < " + i2 + "", connection);

            connection.Open();
            System.Data.OleDb.OleDbDataReader reader = getAccountDetailsCommand.ExecuteReader();
            String[] SqlData = new String[8];
            var writeThisAsHtml = "<table>";
            writeThisAsHtml += "<tr><td>Image</td><td>Product</td><td>catID</td><td>name</td><td>description</td><td>indate</td><td>order</td>";
            while (reader.Read())
            {

                SqlData[0] = reader.GetString(0);
                SqlData[1] = reader.GetString(1);
                SqlData[2] = reader.GetString(2);
                SqlData[3] = reader.GetString(3);
                SqlData[4] = reader.GetString(4);
                SqlData[5] = reader.GetString(5);
                SqlData[6] = reader.GetString(6);
                SqlData[7] = (String)reader.GetValue(7).ToString();
                writeThisAsHtml += "<tr>" + "<td>" + SqlData[5] + "</td> <td>" + SqlData[0] + "</td> <td>" + SqlData[1] + "</td> <td>" + SqlData[2] + "</td> <td>" + SqlData[3] +"</td> <td>"+ SqlData[6] + "</td><td><input name="+SqlData[7] +" type=\"checkbox\"></input></td></tr>";
            }



            writeThisAsHtml += "</table>";
            return writeThisAsHtml;

        }

        public static String getShoppingCartID(String accountID)
        {

            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");

            System.Data.OleDb.OleDbCommand getShoppingCartCommand = new System.Data.OleDb.OleDbCommand("select cartID from ShoppingCart where accountID =" + accountID + "", connection);

            connection.Open();
            System.Data.OleDb.OleDbDataReader reader =  getShoppingCartCommand.ExecuteReader();
            var SqlData = "";
            while (reader.Read())
            {
                SqlData = reader.GetValue(0).ToString();
                break;
            }
            

            return SqlData;
        }
        public static ShoppingCart getShoppingCart(String shoppingCartID)
        {

            System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source= |DataDirectory|\\MyDatabase#1.sdf");

            System.Data.OleDb.OleDbCommand getShoppingCartCommand = new System.Data.OleDb.OleDbCommand("select productID from CartItem where cartID ='" + shoppingCartID + "'", connection);

            connection.Open();
            System.Data.OleDb.OleDbDataReader reader = getShoppingCartCommand.ExecuteReader();
            var shoppingCart = new ShoppingCart();


            while (reader.Read())
            {
                string Temp = reader.GetString(0);
                shoppingCart.addProduct(Temp);
            }

            return shoppingCart;

        }

    }

}

